﻿/*sql server 版数据库存取功能方法
 * 
 * 程序开发：Andy
 * QQ:9502855
 * 版权所有：中华汽配网
 * 开发时间：2007年10月7日
 * 
 * 该程序不提供给客户源码，只提供给客户编译过的文件
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Reflection;


namespace EZCRS.HCMS.DataAcl
{
    public class ExecSql
    {
        public ExecSql()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="">一些特定参数，如表名，字段名，关键字段等</param>
        /// <returns>返回DataSet</returns>
        public static DataSet AddParamFrKeyField(string TblName, string StrGetFields, string FldName, string KeyName, int PageSize, int PageIndex, string StrWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@KeyName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = TblName;
            parameters[1].Value = StrGetFields;
            parameters[2].Value = FldName;
            parameters[3].Value = KeyName;
            parameters[4].Value = PageSize;
            parameters[5].Value = PageIndex;
            parameters[6].Value = StrWhere;
            return ExecSql.GetDataSetByStoredProcedure("hfg_paginationc", parameters);
        }

        /// <summary>
        /// 执行无需返回结果的sql语句
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <returns>返回相应的比数</returns>
        public static int SqlExecNoquery(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(Common.connstr());
            int Result = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                Result = cmd.ExecuteNonQuery();
            }
            catch
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return Result;
            #endregion
        }

        public static int TSqlExecNoquery(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(Common.connstr());
            SqlTransaction trans = null;
            int Result = 0;
            try
            {
                conn.Open();
                trans=conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                cmd.Transaction = trans;
                Result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                trans.Dispose();
            }
            return Result;
            #endregion
        }

        /// <summary>
        /// 执行无需返回结果、带参数的sql语句
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="parm">参数组</param>
        /// <returns></returns>
        public static int SqlExecNoquery(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(Common.connstr());

            conn.Open();
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }
            try
            {
                Result = cmd.ExecuteNonQuery();
            }
            catch
            {
                Result = 0;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return Result;
            #endregion
        }

        public static int TSqlExecNoquery(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(Common.connstr());
            SqlTransaction trans = null;

            conn.Open();
            trans = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            cmd.Transaction = trans;
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }
            try
            {
                Result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                Result = 0;
                trans.Rollback();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                trans.Dispose();
            }
            return Result;
            #endregion
        }

        public static int SqlExecNoqueryByProcedure(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(Common.connstr());
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = SqlStr;


            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }

            try
            {
                conn.Open();
                Result = cmd.ExecuteNonQuery();
            }
            catch
            { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return Result;
            #endregion
        }



        public static bool SqlExecNoqueryByProcedure(string procedurename,out SqlCommand outcmd,params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            outcmd = null;
            SqlConnection conn = new SqlConnection(Common.connstr());
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = procedurename;

            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }

            try
            {
                conn.Open();
                Result = cmd.ExecuteNonQuery();
                outcmd = cmd;
            }
            catch
            { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            
            if (Result > 0)
                return true;
            else
                return false;

            #endregion
        }

        /// <summary>
        /// 执行返回ds的查询
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(Common.connstr());
            DataSet ds = new DataSet();

            try
            {
                SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
                da.Fill(ds);
                da.Dispose();
            }
            catch
            {
            }
            finally
            {
                conn.Dispose();
            }
            return ds;
            #endregion
        }

        

        /// <summary>
        /// 执行带参数的查询，返回ds
        /// </summary>
        /// <param name="SqlStr">sql查询语句</param>
        /// <param name="cmdParms">参数组</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            SqlConnection conn = new SqlConnection(Common.connstr());
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
            foreach (SqlParameter pam in cmdParms)
            {
                da.SelectCommand.Parameters.Add(pam);
            }

			try
			{
                da.Fill(ds);
            }
            catch
            { }
            finally
            {
                da.Dispose();
                conn.Dispose();
            }
            return ds;
            #endregion
        }


        /// <summary>
        /// 执行带参数的查询，返回ds
        /// </summary>
        /// <param name="SqlStr">sql查询语句</param>
        /// <param name="cmdParms">参数组</param>
        /// <returns></returns>
        public static DataSet GetDataSetByStoredProcedure(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            SqlConnection conn = new SqlConnection(Common.connstr());
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter pam in cmdParms)
            {
                da.SelectCommand.Parameters.Add(pam);
            }

            try
            {
                da.Fill(ds);
            }
            catch
            { }
            finally
            {
                da.Dispose();
                conn.Dispose();
            }
            return ds;
            #endregion
        }



        #region  2.0 方法
        /// <summary>
        /// 执行sql语句，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public static List<T> GetTableList<T>(string SqlStr)
        {
            DataTable dt = GetDataSet(SqlStr).Tables[0];
            if (dt == null)
                return null;
            List<T> _t = new List<T>();
            Type _type = typeof(T);

            foreach (DataRow dr in dt.Rows)
            {
                _t.Add((T)Activator.CreateInstance(_type, new object[] { dr }));
            }
            return _t;
        }

        /// <summary>
        /// 执行带参数的Sql语句，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static List<T> GetTableList<T>(string SqlStr, params SqlParameter[] cmdParms)
        {
            DataTable dt = GetDataSet(SqlStr,cmdParms).Tables[0];
            if (dt == null)
                return null;
            List<T> _t = new List<T>();
            Type _type = typeof(T);

            foreach (DataRow dr in dt.Rows)
            {
                _t.Add((T)Activator.CreateInstance(_type, new object[] { dr }));
            }
            return _t;
        }

        /// <summary>
        /// 执行带参数的存储过程，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static List<T> GetTableListByProcedure<T>(string SqlStr, params SqlParameter[] cmdParams)
        {
            DataTable dt = GetDataSetByStoredProcedure(SqlStr, cmdParams).Tables[0];
            if (dt == null)
                return null;
            List<T> _t = new List<T>();
            Type _type = typeof(T);

            foreach (DataRow dr in dt.Rows)
            {
                _t.Add((T)Activator.CreateInstance(_type, new object[] { dr }));
            }
            return _t;
        }
        #endregion
    }

}
